package com.clouds.wms.dao;

import java.util.ArrayList;
import java.util.List;
import javax.annotation.Resource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;
import com.clouds.wms.bean.Cmsmd;
import com.clouds.wms.exception.DatabaseException;
import com.clouds.wms.util.Function;
import com.clouds.wms.util.PagesBean;

@Repository("cmsmdDao")
public class CmsmdDaoImpl implements CmsmdDao {

	private Log log = LogFactory.getLog(getClass());
	
	@Resource(name="namedParameterJdbcTemplate")
	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

	@Override
	public Cmsmd getCmsmdByMdaa(String mdaa) throws DatabaseException {
		String sql = "SELECT md.mdaa,md.mdab,md.mdac,md.mdad,md.mdae,mb.mbab mdad_str,md.mdaf FROM cmsmd md LEFT JOIN cmsmb mb ON md.mdad=mb.mbaa WHERE md.mdaa=?";
		try {
			return namedParameterJdbcTemplate.getJdbcOperations().queryForObject(sql, new Object[]{mdaa}, new BeanPropertyRowMapper<Cmsmd>(Cmsmd.class));
		} catch (EmptyResultDataAccessException e) {
			log.info("没有数据");
			return null;
		} catch (Exception e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}
	
	
	@Override
	public PagesBean getCmsmdList(String mdaa, String mdac, String mdad, int offset, int pageSize) throws DatabaseException {
		PagesBean pb = new PagesBean();
		List<Cmsmd> list = new ArrayList<Cmsmd>();
		boolean hasWhere = false;
		StringBuffer sql = new StringBuffer("SELECT md.mdaa,md.mdab,md.mdac,md.mdad,md.mdae,mb.mbab mdad_str,md.mdaf FROM cmsmd md LEFT JOIN cmsmb mb ON md.mdad=mb.mbaa ");
		if(mdaa != null && !"".equals(mdaa)) {
			hasWhere = Function.appendWhereIfNeed(sql, hasWhere);
			sql.append(" mdaa LIKE '%").append(Function.formatSQLString(mdaa)).append("%'");
		}
		if(mdac != null && !"".equals(mdac)) {
			hasWhere = Function.appendWhereIfNeed(sql, hasWhere);
			sql.append(" mdac LIKE '%").append(Function.formatSQLString(mdac)).append("%'");	
		}
		if(mdad != null && !"".equals(mdad)) {
			hasWhere = Function.appendWhereIfNeed(sql, hasWhere);
			sql.append(" mdad LIKE '%").append(Function.formatSQLString(mdad)).append("%'");
		}
		sql.append(" ORDER BY mdaa DESC");
		
		try {
			int totalCount = namedParameterJdbcTemplate.getJdbcOperations().queryForInt(Function.toCountSql(sql.toString()));
			if(totalCount!=0){
				list = namedParameterJdbcTemplate.getJdbcOperations().query(Function.mySqlQuery(sql.toString(),offset,pageSize,totalCount),new BeanPropertyRowMapper<Cmsmd>(Cmsmd.class));
			}
			pb.setCount(totalCount);
			pb.setData(list);
			return pb;
		} catch (EmptyResultDataAccessException e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}

	@Override
	public int saveCmsmd(Cmsmd cmsmd) throws DatabaseException {
		String sql = "INSERT INTO cmsmd (mdaa,mdab,mdac,mdad,mdae,mdaf) VALUES (:mdaa,:mdab,:mdac,:mdad,:mdae,:mdaf)";
		try {
			SqlParameterSource param = new BeanPropertySqlParameterSource(cmsmd);
			return namedParameterJdbcTemplate.update(sql, param);
		} catch (Exception e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}

	@Override
	public int updateCmsmd(Cmsmd cmsmd) throws DatabaseException {
		String sql = "UPDATE cmsmd SET mdac=:mdac,mdad=:mdad,mdae=:mdae,mdaf=:mdaf WHERE mdaa=:mdaa";
		try {
			SqlParameterSource param = new BeanPropertySqlParameterSource(cmsmd);
			return namedParameterJdbcTemplate.update(sql, param);
		} catch (Exception e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}

	@Override
	public int deleteCmsmdByMdaa(String mdaa) throws DatabaseException {
		String sql = "DELETE FROM cmsmd WHERE mdaa=?";
		try {
			return namedParameterJdbcTemplate.getJdbcOperations().update(sql, new Object[]{mdaa});
		} catch (Exception e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}

	@Override
	public int updateCmsmdMdab(String mdaa, String mdab) throws DatabaseException {
		String sql = "UPDATE cmsmd SET mdab=? WHERE mdaa=?";
		try {
			return namedParameterJdbcTemplate.getJdbcOperations().update(sql, new Object[]{mdab, mdaa});
		} catch (Exception e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}

	@Override
	public List<Cmsmd> getAllCmsmdList() throws DatabaseException {
		String sql = "SELECT md.mdaa,md.mdab,md.mdac,md.mdad,md.mdae,mb.mbab mdad_str,md.mdaf FROM cmsmd md LEFT JOIN cmsmb mb ON md.mdad=mb.mbaa";
		try {
			return namedParameterJdbcTemplate.getJdbcOperations().query(sql.toString(), new BeanPropertyRowMapper<Cmsmd>(Cmsmd.class));
		} catch (Exception e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}
	
}
